package com.gl.basis.common.poi;

 
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.gl.basis.common.poi.annotation.Excel2k3Base;
import com.gl.basis.common.poi.annotation.Excel2k7Base;
import com.gl.basis.common.poi.annotation.ExcelAnnotation;
import com.gl.basis.common.poi.annotation.ExcelBase;
import com.gl.basis.common.poi.annotation.ExcelUtils;

 
/**
 * 导出Excel工具类
 * 
 * @author 
 *
 */
public class ExcelExportUtil {
    
    private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);
    
    /** 日期格式 yyyy-MM-dd*/
    private static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
    /** 日期格式yyyy-MM-dd HH:mm:ss*/
    private static final DateFormat DATE_FORMAT_YYYY_MM_DD_HH_MM_SS=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    /**
     * 	单元格映射
     */
    public static class CellMap {
        private String title;// 标题
        private String property;// 属性
        
        public CellMap(String title, String property) {
            this.title = title;
            this.property = property;
        }
 
        public String getTitle() {
            return title;
        }
 
        public void setTitle(String title) {
            this.title = title;
        }
 
        public String getProperty() {
            return property;
        }
 
        public void setProperty(String property) {
            this.property = property;
        }
 
    }
 
    /**
     * 导出Excel
     * @param cellMapList 单元格映射列表
     * @param dataList 数据列表
     * @param rowAccessWindowSize 内存中缓存记录数
     * @param out 输出流
     * @throws Exception
     */
    public static void exportSXSSFExcel( String fileName ,List<CellMap> cellMapList, List<?> dataList, HttpServletResponse response) throws Exception {
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        Sheet sheet =null;//工作表对象
        Row row = null;//行对象
        Cell cell = null;//列对象
        
		int rowNo = 0;		//总行号
		int pageRowNo = 0;	//页行号
	    int index = 0;
        
        if (cellMapList == null || cellMapList.size() <= 0) {
            throw new Exception("单元格列表不能为空");
        }
        if (dataList == null || dataList.size() <= 0) {
        	throw new Exception("数据不能为空");
        }
        
        int rowSize = (dataList == null) ? 0 : dataList.size();
		for (int i = 0; i < rowSize; i++) {

			// 打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
			if (rowNo % 30000 == 0) {
				System.out.println("Current Sheet:" + rowNo / 30000);
				sheet = workbook.createSheet("Sheet" + rowNo / 30000 );// 建立新的sheet对象
				sheet = workbook.getSheetAt(rowNo / 30000); // 动态指定当前的工作表
				pageRowNo = 0; // 每当新建了工作表就将当前工作表的行号重置为0
				index = 0;
			}
			rowNo++;
			index++;

			if (index == 1) {
				// 标题
				Font titleFont = workbook.createFont();
				titleFont.setBold(true);
				CellStyle titleCellStyle = workbook.createCellStyle();
				titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
				titleCellStyle.setFont(titleFont);
				row = sheet.createRow(pageRowNo++);
				int cellSize = cellMapList.size();
				for (int j = 0; j < cellSize; j++) {
					CellMap cellMap = cellMapList.get(j);
					String title = cellMap.getTitle();
					cell = row.createCell(j);
					cell.setCellStyle(titleCellStyle);
					cell.setCellValue(title);
					if (title != null) {
						sheet.setColumnWidth(j, title.getBytes().length * 2 * 172);
					}
				}
			}

			// 数据
			CellStyle dataCellStyle = workbook.createCellStyle();
			dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
			Object obj = dataList.get(i);
			row = sheet.createRow(pageRowNo++);
			int cellSize = cellMapList.size();
			for (int j = 0; j < cellSize; j++) {
				CellMap cellMap = cellMapList.get(j);
				cell = row.createCell(j);
				cell.setCellStyle(dataCellStyle);
				String property = cellMap.getProperty();
				if (property.equals("rowNumber") || StringUtils.isEmpty(property)) {
					cell.setCellValue(i);
				} else {
					String propertyValue = getPropertyValue(obj, property);
					cell.setCellValue(propertyValue);
					if (propertyValue != null) {
						int columnWidth = sheet.getColumnWidth(j);
						int propertyValueLength = propertyValue.getBytes().length * 2 * 172;
						if (columnWidth < propertyValueLength) {
							sheet.setColumnWidth(j, propertyValueLength);
						}
					}
				}

			}
		}
		
		
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		workbook.write(os);
		workbook.dispose();
        response.setContentType("application/octet-stream");
        String returnName = response.encodeURL(new String(fileName.getBytes(),"iso8859-1"));
        response.setHeader("content-disposition", "attachment;filename="+returnName);
        OutputStream out = response.getOutputStream();
        response.setContentLength(os.size());
        os.writeTo(out);
        out.close();
        out.flush();
    }
    
    
    /**
     * 导出Excel
     * @param cellMapList 单元格映射列表
     * @param dataList 数据列表
     * @param rowAccessWindowSize 内存中缓存记录数
     * @param out 输出流
     * @throws Exception
     */
    public static void exportSXSSFExcelFromMapData(String sheetName, List<CellMap> cellMapList, List<Map<String,String>> dataList, int rowAccessWindowSize, OutputStream out) throws Exception {
        SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
        Sheet sheet = workbook.createSheet(sheetName);
        Row row = null;
        Cell cell = null;
        if (cellMapList == null || cellMapList.size() <= 0) {
            throw new Exception("cellMapList不能为空或小于等于0");
        }
        int rowIndex = 0;
        // 标题
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setFont(titleFont);
        row = sheet.createRow(rowIndex++);
        int cellSize = cellMapList.size();
        for (int i = 0; i < cellSize; i++) {
            CellMap cellMap = cellMapList.get(i);
            String title = cellMap.getTitle();
            cell = row.createCell(i);
            cell.setCellStyle(titleCellStyle);
            cell.setCellValue(title);
            if (title != null) {
                sheet.setColumnWidth(i, title.getBytes().length * 2 * 172);
            }
        }
        // 数据
        CellStyle dataCellStyle = workbook.createCellStyle();
        dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
        int rowSize = (dataList == null) ? 0 : dataList.size();
        for (int i = rowIndex; i < rowSize + rowIndex; i++) {
            Map<String,String> obj = dataList.get(i - rowIndex);
            row = sheet.createRow(i);
            for (int j = 0; j < cellSize; j++) {
                CellMap cellMap = cellMapList.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(dataCellStyle);
                String property = cellMap.getProperty();
                if(property.equals("rowNumber") || StringUtils.isEmpty(property)){
                	cell.setCellValue(i);
                }else{
//                	String propertyValue = getPropertyValue(obj, property);
                	String propertyValue = obj.get(property);
                	cell.setCellValue(propertyValue);
                	if (propertyValue != null) {
                		int columnWidth = sheet.getColumnWidth(j);
                		int propertyValueLength = propertyValue.getBytes().length * 2 * 172;
                		if (columnWidth < propertyValueLength) {
                			sheet.setColumnWidth(j, propertyValueLength);
                		}
                	}
                }
                	
            }
        }
        workbook.write(out);
    }
    
    /**
     * 获取属性值
     * @param obj
     * @param property
     * @return
     * @throws Exception
     */
    private static String getPropertyValue(Object obj, String property) throws Exception {
        if (obj instanceof Map)
        {
            Object val = ((Map<String,Object>)obj).get(StringUtils.upperCase(property));
            if (val==null)
            {
                return "";
            }
            return val.toString();
        }
        Object result = null;
        String str = "";
        Class<?> clazz = obj.getClass();
        if (property == null || "".equals(property)) {
            return "";
        }
        Method readMethod = clazz.getMethod("get" + property.substring(0, 1).toUpperCase() + property.substring(1));
        if (readMethod != null) {
            result = readMethod.invoke(obj);
        }
        if (result != null) {
            if (result.getClass() == Date.class) {
                str = dateFormat.format(result);
            } else {
                str = result.toString();
            }
        } else {
            str = "";
        }
        return str;
    }
    
    
    public static void main(String[] args) {
        List<CellMap> cellMapList = new ArrayList<CellMap>();
        cellMapList.add(new CellMap("单元格1", "cell1"));
        cellMapList.add(new CellMap("单元格2", "cell2"));
        cellMapList.add(new CellMap("单元格3", "cell3"));
        cellMapList.add(new CellMap("单元格4", "date"));
      //  List<Excel> dataList = new ArrayList<>();
       // dataList.add(new Excel("1223","13241","1234123",new Date()));
       
        
        try {
          // ExcelExportUtil.exportSXSSFExcel("Sheet1",cellMapList, dataList, 1000, new FileOutputStream(new File("d:/test.xlsx")));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 
     * 填充excel数据
     * <功能详细描述>
     * @param dataCellStyle
     * @param cellSize
     * @param sheet
     * @param rowIndex
     * @param workbook
     * @param response
     * @param excelName
     * @param cellMapList
     * @param dataList
     * @return
     * @throws Exception
     * @see [类、类#方法、类#成员]
     */
    public static int fillExcel_2007_SXSSF(CellStyle dataCellStyle,int cellSize,Sheet sheet,int rowIndex,SXSSFWorkbook workbook,HttpServletResponse response,String excelName, List<CellMap> cellMapList, List<?> dataList) throws Exception{
        Row row = null;
        Cell cell = null;
        int rowSize = (dataList == null) ? 0 : dataList.size();
        for (int i = rowIndex; i < rowSize + rowIndex; i++) {
            Object obj = dataList.get(i - rowIndex);
            row = sheet.createRow(i);
            for (int j = 0; j < cellSize; j++) {
                CellMap cellMap = cellMapList.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(dataCellStyle);
                String property = cellMap.getProperty();
                if(property.equals("rowNumber") || StringUtils.isEmpty(property)){
                    cell.setCellValue(i);
                }else{
                    String propertyValue = getPropertyValue(obj, property);
                    cell.setCellValue(propertyValue);
                    if (propertyValue != null) {
                        int columnWidth = sheet.getColumnWidth(j);
                        int propertyValueLength = propertyValue.getBytes().length * 2 * 172;
                        if (columnWidth < propertyValueLength) {
//                            sheet.setColumnWidth(j, propertyValueLength);
                        }
                    }
                }
            }
        }
        return rowSize + rowIndex;
    }
    
 //===========================================导入======================================================================   

    
    
    
    
    // excel2003扩展名
    public static final String EXCEL03_EXTENSION = ".xls";
    // excel2007扩展名
    public static final String EXCEL07_EXTENSION = ".xlsx";

    // 获取Excel处理类
    private static ExcelBase getExcelHelper(String fileName) {
        ExcelBase helper = null;
        if (fileName.endsWith(EXCEL03_EXTENSION)) {
            helper = new Excel2k3Base();
        } else if (fileName.endsWith(EXCEL07_EXTENSION)) {
            helper = new Excel2k7Base();
        }
        return helper;
    }
    
    
    
    /** 
	 * excel导入
	 * 
     * @param fileName 文件名
     * @param pojoClass 要导出的pojo
     * @param headRow 表头在哪一行,最小为1,即第一行;
     * @return
     */
	public static <T> List<T> impot(String fileName,InputStream fileIs,Class<T> pojoClass,int headRow){
		if(fileName==null || fileName.trim().length() == 0) throw new RuntimeException("导入的excel文件名为空!");
		if(fileIs==null) throw new RuntimeException("导入的Excel数据为空!");
		try {
			List<T> rsLst = ExcelUtils.importExcelInConmmissionUser(fileName, fileIs, pojoClass, headRow-1);
			if(rsLst==null || rsLst.size()==0) throw new Exception("导入的Excel数据为空!");
			return rsLst;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("导入excel失败!"+e.getMessage());
		}
	}
    
    /**
     * @param pojoClass
     * @param rowspan 跳过多少行
     * @return
     */
    public static <T> List<T> importExcelInConmmissionUser(String fileName, InputStream fileIs, Class<T> pojoClass, int rowspan) throws Exception {
    	if(rowspan<0) rowspan = 0;
        List dist = new ArrayList<Object>();
        // 得到目标目标类的所有的字段列表
        Field filed[] = pojoClass.getDeclaredFields();
        // 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
        Map<String, Method> fieldSetMap = new HashMap<String, Method>();
        Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>();
        // 循环读取所有字段
        for (int i = 0; i < filed.length; i++) {
            Field f = filed[i];
            // 得到单个字段上的Annotation
            ExcelAnnotation excel = f.getAnnotation(ExcelAnnotation.class);
            // 如果标识了Annotationd的话
            if (excel != null) {
                // 构造设置了Annotation的字段的Setter方法
                String fieldname = f.getName();
                String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
                // 构造调用的method，
                Method setMethod = pojoClass.getMethod(setMethodName, new Class[] { f.getType() });
                // 将这个method以Annotaion的名字为key来存入。
                // 对于重名将导致 覆盖 失败，对于此处的限制需要
                fieldSetMap.put(excel.name(), setMethod);
                if (excel.importConvertSign() == 1) {
                    StringBuffer setConvertMethodName = new StringBuffer("set");
                    setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
                    setConvertMethodName.append(fieldname.substring(1));
                    setConvertMethodName.append("Convert");
                    Method getConvertMethod = pojoClass.getMethod(setConvertMethodName.toString(),
                            new Class[] { String.class });
                    fieldSetConvertMap.put(excel.name(), getConvertMethod);
                }
            }
        }

        ExcelBase excelHelper = getExcelHelper(fileName);
        // // 得到工作表
        Workbook book = excelHelper.readWorkbookFromExcel(fileIs);
        // // 得到第一页
        Sheet sheet = book.getSheetAt(0);
        // // 得到第一面的所有行
        Iterator<Row> row = sheet.rowIterator();

        // 跳过多少行
        for (int i = 0; i < rowspan; i++) {
            row.next();
        }
        
        // 得到表头行
        Row head = row.next();
        Iterator<Cell> cellTitle = head.cellIterator();
        // 将表头的列放入到一个map中。
        Map headMap = new HashMap();
        int i = 0;
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = String.valueOf(cell.getStringCellValue());
            headMap.put(i, value);
            i = i + 1;
        }
        
        //解析数据行
        while (row.hasNext()) {
            Row rown = row.next();
            if(ExcelUtils.isBlankRow(rown)){
                continue;
            }
            // 行的所有列
            Iterator<Cell> cellbody = rown.cellIterator();
            // 得到传入类的实例
            Object tObject = pojoClass.newInstance();
          
            for (int j = rown.getFirstCellNum(); j <= rown.getLastCellNum(); j++) {
                Cell cell = rown.getCell(j);
                if(cell==null){ continue; }
                String titleString = (String) headMap.get(j);
                // 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
                if (!fieldSetMap.containsKey(titleString)) continue;
                
                Method setMethod = (Method) fieldSetMap.get(titleString);
                // 得到setter方法的参数
                Type[] ts = setMethod.getGenericParameterTypes();
                // 只要一个参数
                String xclass = ts[0].toString();
                
                try{
	                // 如果是转换的方法就直接执行转换的方法
	                if (fieldSetConvertMap.containsKey(titleString)) {
	                	if(cell.getStringCellValue()==null) continue;
	                	fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue().trim());
	                	continue;
	                } 
	                
	                //设置值
	                if (xclass.equals("class java.lang.String")) {
	                    // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了：
	                    cell.setCellType(CellType.STRING);
	                    if (cell.getStringCellValue()!=null && cell.getStringCellValue().trim().length()!=0) {
	                    	String temp =cell.getStringCellValue().trim();
	                        setMethod.invoke(tObject,temp);
	                    }
	                    continue;
	                } 
	                
	                if (xclass.equals("class java.util.Date")) {
	                	setMethod.invoke(tObject, cell.getDateCellValue());
	                } 
	                
	                if (xclass.equals("class java.lang.Boolean")) {
	                    if (cell.getCellType() == CellType.BOOLEAN) {
	                        setMethod.invoke(tObject, cell.getBooleanCellValue());
	                    }
	                    continue;
	                } 
	                
	                if (xclass.equals("class java.lang.Integer")) {
	                    if (cell.getCellType() == CellType.NUMERIC) {
	                        setMethod.invoke(tObject, new Integer((int) cell.getNumericCellValue()));
	                    } else if (cell.getCellType() ==CellType.STRING) {
	                    	if(cell.getStringCellValue()==null) continue;
	                        setMethod.invoke(tObject, new Integer(cell.getStringCellValue().trim()));
	                    }
	                    continue;
	                } 
	                
	                if (xclass.equals("class java.lang.Long")) {
	                    if (cell.getCellType() == CellType.NUMERIC) {
	                        setMethod.invoke(tObject, new Long((long) cell.getNumericCellValue()));
	                    } else if (cell.getCellType() ==CellType.STRING) {
	                    	if(cell.getStringCellValue()==null) continue;
	                        setMethod.invoke(tObject, new Long(cell.getStringCellValue().trim()));
	                    }
	                    continue;
	                } 
	                
	                if (("class java.math.BigDecimal").equals(xclass)) {
	                    Double number = 0.0;
	                    // 判断cell类型是否为number型
	                    if (cell.getCellType() != CellType.NUMERIC) {
	                    	if(cell.getStringCellValue()==null) continue;
	                        number = Double.valueOf(cell.getStringCellValue().trim());
	                    } else {
	                        number = Double.valueOf(cell.getNumericCellValue());
	                    }
	                    setMethod.invoke(tObject, new BigDecimal(number.toString()));
	                    continue;
	                }
                }catch(Exception e){
                	e.printStackTrace();
                }
            }
            
            if (tObject != null) {  dist.add(tObject); }
        }
        return dist;
    }
    
}
   
